In [101]:
import plotly.io as pio
pio.renderers.default='notebook'
In [102]:
!pip install Pyppeteer
!pyppeteer-install

Defaulting to user installation because normal site-packages is not writeable
  WARNING: The script pyppeteer-install.exe is installed in 'C:\Users\user\AppData\Roaming\Python\Python311\Scripts' which is not on PATH.
  Consider adding this directory to PATH or, if you prefer to suppress this warning, use --no-warn-script-location.

Collecting Pyppeteer
  Obtaining dependency information for Pyppeteer from https://files.pythonhosted.org/packages/3d/ee/fb2757a38025421fd3844a0ed0a230b78c9c04a66355024436cf3005a70c/pyppeteer-2.0.0-py3-none-any.whl.metadata
  Downloading pyppeteer-2.0.0-py3-none-any.whl.metadata (7.1 kB)
Requirement already satisfied: appdirs<2.0.0,>=1.4.3 in c:\programdata\anaconda3\lib\site-packages (from Pyppeteer) (1.4.4)
Requirement already satisfied: certifi>=2023 in c:\programdata\anaconda3\lib\site-packages (from Pyppeteer) (2023.7.22)
Requirement already satisfied: importlib-metadata>=1.4 in c:\programdata\anaconda3\lib\site-packages (from Pyppeteer) (6.0.0)
Collecting pyee<12.0.0,>=11.0.0 (from Pyppeteer)
  Obtaining dependency information for pyee<12.0.0,>=11.0.0 from https://files.pythonhosted.org/packages/16/cc/5cea8a0a0d3deb90b5a0d39ad1a6a1ccaa40a9ea86d793eb8a49d32a6ed0/pyee-11.1.0-py3-none-any.whl.metadata
  Downloading pyee-11.1.0-py3-none-any.whl.metadata (2.8 kB)
Requirement already satisfied: tqdm<5.0.0,>=4.42.1 in c:\programdata\anaconda3\lib\site-packages (from Pyppeteer) (4.65.0)
Requirement already satisfied: urllib3<2.0.0,>=1.25.8 in c:\programdata\anaconda3\lib\site-packages (from Pyppeteer) (1.26.16)
Collecting websockets<11.0,>=10.0 (from Pyppeteer)
  Obtaining dependency information for websockets<11.0,>=10.0 from https://files.pythonhosted.org/packages/27/bb/6327e8c7d4dd7d5b450b409a461be278968ce05c54da13da581ac87661db/websockets-10.4-cp311-cp311-win_amd64.whl.metadata
  Downloading websockets-10.4-cp311-cp311-win_amd64.whl.metadata (6.4 kB)
Requirement already satisfied: zipp>=0.5 in c:\programdata\anaconda3\lib\site-packages (from importlib-metadata>=1.4->Pyppeteer) (3.11.0)
Requirement already satisfied: typing-extensions in c:\programdata\anaconda3\lib\site-packages (from pyee<12.0.0,>=11.0.0->Pyppeteer) (4.7.1)
Requirement already satisfied: colorama in c:\programdata\anaconda3\lib\site-packages (from tqdm<5.0.0,>=4.42.1->Pyppeteer) (0.4.6)
Downloading pyppeteer-2.0.0-py3-none-any.whl (82 kB)
   ---------------------------------------- 0.0/82.9 kB ? eta -:--:--
   -------------- ------------------------- 30.7/82.9 kB 1.3 MB/s eta 0:00:01
   -------------- ------------------------- 30.7/82.9 kB 1.3 MB/s eta 0:00:01
   -------------- ------------------------- 30.7/82.9 kB 1.3 MB/s eta 0:00:01
   ----------------------------- ---------- 61.4/82.9 kB 326.1 kB/s eta 0:00:01
   ---------------------------------------- 82.9/82.9 kB 331.7 kB/s eta 0:00:00
Downloading pyee-11.1.0-py3-none-any.whl (15 kB)
Downloading websockets-10.4-cp311-cp311-win_amd64.whl (101 kB)
   ---------------------------------------- 0.0/101.4 kB ? eta -:--:--
   ---- ----------------------------------- 10.2/101.4 kB ? eta -:--:--
   ------------------------ --------------- 61.4/101.4 kB 1.1 MB/s eta 0:00:01
   -------------------------------------- 101.4/101.4 kB 829.1 kB/s eta 0:00:00
Installing collected packages: websockets, pyee, Pyppeteer
Successfully installed Pyppeteer-2.0.0 pyee-11.1.0 websockets-10.4

'pyppeteer-install' is not recognized as an internal or external command,
operable program or batch file.
In [55]:
import pyodbc
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
import plotly.express as px
import plotly.graph_objects as go
In [2]:
cnxn_str =("Driver={ODBC Driver 17 for SQL Server};"
                      "Server=LAPTOP-PU7MK2SG\IMAN;"
                      "Database=imanportfolio2;"
                      "Trusted_Connection=yes;")
In [3]:
cnxn = pyodbc.connect(cnxn_str)
In [4]:
cursor = cnxn.cursor()
In [5]:
query = """

select *
from tsecurity_data..qty
order by Product

"""

qty_df = pd.read_sql_query(query, cnxn)
qty_df

C:\Users\user\AppData\Local\Temp\ipykernel_72\1367283235.py:9: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
  qty_df = pd.read_sql_query(query, cnxn)
Out[5]:
Product Store_number Jan23 Feb23 Mar23 Apr23 May23 Jun23 Jul23 Aug23 ... Mar22 Apr22 May22 Jun22 Jul22 Aug22 Sep22 Oct22 Nov22 Dec22
0 A 1.0 0.0 0.0 4.0 0.0 5.0 0.0 5.0 0.0 ... 10.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
1 A 2.0 5.0 0.0 5.0 0.0 0.0 0.0 0.0 5.0 ... 5.0 5.0 0.0 6.0 5.0 0.0 2.0 5.0 0.0 0.0
2 A 3.0 5.0 10.0 50.0 10.0 30.0 10.0 30.0 15.0 ... 20.0 35.0 20.0 27.0 21.0 0.0 20.0 15.0 15.0 20.0
3 A 4.0 20.0 0.0 20.0 0.0 30.0 0.0 0.0 40.0 ... 10.0 20.0 10.0 20.0 20.0 0.0 20.0 20.0 0.0 10.0
4 A 5.0 0.0 20.0 0.0 25.0 20.0 10.0 0.0 20.0 ... 10.0 10.0 10.0 10.0 0.0 20.0 0.0 0.0 20.0 5.0
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
5388 H 1027.0 0.0 0.0 3.0 0.0 0.0 0.0 0.0 0.0 ... 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
5389 H 1029.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 ... 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
5390 H 1030.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 ... 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
5391 H 1031.0 0.0 0.0 0.0 0.0 0.0 10.0 0.0 0.0 ... 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
5392 H 1032.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 10.0 ... 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0

5393 rows × 23 columns

In [91]:
query = """

SELECT 
    Product,
    SUM(Jan23 + Feb23 + Mar23) AS Q1_2023,
    SUM(Apr23 + May23 + Jun23) AS Q2_2023,
    SUM(Jul23 + Aug23 + Sep23) AS Q3_2023,
    SUM(Jan22 + Feb22 + Mar22) AS Q1_2022,
    SUM(Apr22 + May22 + Jun22) AS Q2_2022,
    SUM(Jul22 + Aug22 + Sep22) AS Q3_2022,
    SUM(Oct22 + Nov22 + Dec22) AS Q4_2022
FROM tsecurity_data..qty
GROUP BY Product
order by Product

    
"""
quarterly_product_qty_df = pd.read_sql_query(query, cnxn)
quarterly_product_qty_df

C:\Users\user\AppData\Local\Temp\ipykernel_72\2849456373.py:18: UserWarning:

pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
Out[91]:
Product Q1_2023 Q2_2023 Q3_2023 Q1_2022 Q2_2022 Q3_2022 Q4_2022
0 A 25207.0 39027.0 12027.0 12566.0 39633.0 21195.0 15865.0
1 B 27326.0 38924.0 8879.0 10670.0 39649.0 20792.0 13721.0
2 C 19787.0 28970.0 8792.0 9533.0 30461.0 15367.0 11265.0
3 D 12520.0 18750.0 4920.0 6145.0 19812.0 9570.0 7270.0
4 E 1557.0 2376.0 766.0 664.0 2119.0 1157.0 870.0
5 F 894.0 3703.0 3024.0 0.0 0.0 0.0 2632.0
6 G 3919.0 7422.0 6995.0 0.0 0.0 0.0 6011.0
7 H 469.0 1340.0 1433.0 0.0 0.0 0.0 1784.0
In [99]:
quarters = ['Q1_2023', 'Q2_2023', 'Q3_2023', 'Q1_2022', 'Q2_2022', 'Q3_2022', 'Q4_2022']

# Create traces for each quarter
traces = []
for quarter in quarters:
    trace = go.Bar(
        x=quarterly_product_qty_df['Product'],
        y=quarterly_product_qty_df[quarter],
        name=quarter
    )
    traces.append(trace)

# Define layout
layout = go.Layout(
    title='Quarterly Quantity by Product',
    xaxis=dict(title='Product'),
    yaxis=dict(title='Quantity'),
    barmode='group'
)

# Create figure
fig = go.Figure(data=traces, layout=layout)

# Show plot
fig.show()
In [8]:
query = """

select Product, 
(sum(Jan23) + sum(Feb23) + sum(Mar23) + sum(Apr23) +
sum(May23)+ sum(Jun23) + sum(Jul23) + sum(Aug23) + sum(Sep23))total_qty_2023,
(sum(Jan22) + sum(Feb22) + sum(Mar22) + sum(Apr22) +
sum(May22)+ sum(Jun22) + sum(Jul22) + sum(Aug22) + sum(Sep22))total_qty_2022
from tsecurity_data..qty
group by Product
order by (sum(Jan23) + sum(Feb23) + sum(Mar23) + sum(Apr23) +
sum(May23)+ sum(Jun23) + sum(Jul23) + sum(Aug23) + sum(Sep23)) desc

"""
yearly_product_qty_df= pd.read_sql_query(query, cnxn)
yearly_product_qty_df

C:\Users\user\AppData\Local\Temp\ipykernel_72\3307530111.py:14: UserWarning:

pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
Out[8]:
Product total_qty_2023 total_qty_2022
0 A 76261.0 73394.0
1 B 75129.0 71111.0
2 C 57549.0 55361.0
3 D 36190.0 35527.0
4 G 18336.0 0.0
5 F 7621.0 0.0
6 E 4699.0 3940.0
7 H 3242.0 0.0
In [51]:
products = yearly_product_qty_df['Product']
qty_2023 = yearly_product_qty_df['total_qty_2023']
qty_2022 = yearly_product_qty_df['total_qty_2022']

# Set the width of the bars
bar_width = 0.35

# Set the positions of the bars on the x-axis
r1 = np.arange(len(products))
r2 = [x + bar_width for x in r1]

# Create the bar plots
plt.bar(r1, qty_2023, color='skyblue', width=bar_width, edgecolor='grey', label='Quantity Sold in 2023')
plt.bar(r2, qty_2022, color='lightgreen', width=bar_width, edgecolor='grey', label='Quantity Sold in 2022')

# Add xticks on the middle of the group bars
plt.xlabel('Product', fontweight='bold')
plt.ylabel('Quantity Sold', fontweight='bold')
plt.xticks([r + bar_width/2 for r in range(len(products))], products)

# Add title and legend
plt.title('Quantity Sold in 2023 vs 2022')
plt.legend()

# Show plot
plt.show()
In [9]:
#yearly_qty_df['norm_total_qty_2023']= (yearly_qty_df['total_qty_2023']/3242)

#yearly_qty_df['norm_total_qty_2022']= (yearly_qty_df['total_qty_2022']/3940)

fig = go.Figure()

# Add bar trace for quantity sold in 2023
fig.add_trace(go.Bar(
    x=yearly_product_qty_df['Product'],
    y=yearly_product_qty_df['total_qty_2023'],
    name='Quantity Sold in 2023'
))

# Add bar trace for quantity sold in 2022
fig.add_trace(go.Bar(
    x=yearly_product_qty_df['Product'],
    y=yearly_product_qty_df['total_qty_2022'],
    name='Quantity Sold in 2022'
))

# Update layout
fig.update_layout(
    title='Quantity Sold in 2023 vs 2022',
    xaxis_title='Product',
    yaxis_title='Quantity Sold',
    barmode='group'
)

# Show plot
fig.show()
In [10]:
query= """

select  
(sum(Jan23) + sum(Feb23) + sum(Mar23) + sum(Apr23) +
sum(May23)+ sum(Jun23) + sum(Jul23) + sum(Aug23) + sum(Sep23))total_qty_2023,
(sum(Jan22) + sum(Feb22) + sum(Mar22) + sum(Apr22) +
sum(May22)+ sum(Jun22) + sum(Jul22) + sum(Aug22) + sum(Sep22))total_qty_2022
from tsecurity_data..qty
order by (sum(Jan23) + sum(Feb23) + sum(Mar23) + sum(Apr23) +
sum(May23)+ sum(Jun23) + sum(Jul23) + sum(Aug23) + sum(Sep23)) desc

"""
yearly_qty_df = pd.read_sql_query(query, cnxn)
yearly_qty_df

C:\Users\user\AppData\Local\Temp\ipykernel_72\3933393213.py:13: UserWarning:

pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
Out[10]:
total_qty_2023 total_qty_2022
0 279027.0 239333.0
In [16]:
import pandas as pd
import plotly.graph_objects as go

total_qty_2023 = yearly_qty_df['total_qty_2023'].sum()
total_qty_2022 = yearly_qty_df['total_qty_2022'].sum()

fig = go.Figure(data=[go.Pie(labels=['Total Quantity Sold in 2023', 'Total Quantity Sold in 2022'],
                             values=[total_qty_2023, total_qty_2022])])

fig.update_layout(title='Total Quantity Sold in 2023 vs 2022')

fig.show()
In [22]:
query="""
select Product, count(Product)selling_frequency
from tsecurity_data..qty
group by Product
order by count(Product) desc
"""

product_count_df=pd.read_sql_query(query,cnxn)
product_count_df

C:\Users\user\AppData\Local\Temp\ipykernel_72\1722162897.py:8: UserWarning:

pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
Out[22]:
Product selling_frequency
0 A 911
1 B 901
2 C 887
3 D 843
4 G 605
5 F 561
6 H 430
7 E 255
In [49]:
fig = go.Figure(data=[go.Bar(x=product_count_df['Product'],
                             y=product_count_df['selling_frequency'])])

fig.update_layout(
    title='Product Selling Frequency',
    xaxis_title ='Product',
    yaxis_title ='Selling Frequency',
    bargap=0.05)
fig.show()

三個我認為有價值的發現¶

¶